package mygang.dao.manager;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import mygang.model.ColumnModel;
import mygang.model.TableModel;
import mygang.model.constructor.TableWizardModel;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author Volodymyr Lavrynovych <volodya.lavrynovych@gmail.com>
 * date: 23/02/2013
 */
@Repository("tableDAO")
@Transactional
public class TableDAOImpl extends AbstractManageDAO<TableWizardModel> implements TableDAO {

    public static final String PRIMARY_KEY = "PRI";
    private static final Logger log = Logger.getLogger(TableDAOImpl.class.toString());
    
    public TableDAOImpl(){}

    public List<TableModel> getTableList() {
        List<TableModel> tables = new ArrayList<TableModel>();
        
        Query query = getSession().createSQLQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = :TABLESCHEMA");
        query.setParameter("TABLESCHEMA", getProperty(NAME));
        
        List result = query.list();
        log.debug("result.size() = "+result.size());
        for(Object name : result){
            log.debug((String)name);
            tables.add(new TableModel((String)name));
        }
        return tables;
    }

    public List<ColumnModel> getTableColumns(String tableName) {
        List<ColumnModel> columns = new ArrayList<ColumnModel>();
        
        Query query = getSession().createSQLQuery("SELECT COLUMN_NAME, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = :TABLENAME AND TABLE_SCHEMA = :TABLESCHEMA");
        
        query.setParameter("TABLENAME", tableName);
        query.setParameter("TABLESCHEMA", getProperty(NAME));
        List result = query.list();
        log.debug("result.size() = "+result.size());
        
        for (Iterator iter = result.iterator(); iter.hasNext();) {
            Object[] row = (Object[]) iter.next();
            String name = (String) row[0];
            String key = (String) row[0];
            columns.add(new ColumnModel(name, key.equals(PRIMARY_KEY)));
        }
        return columns;
    }

    public TableModel getTableByName(String tableName) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void delete(TableWizardModel table) {
        getHibernateTemplate().delete(table);
    }

    public String getTablePrimaryColumn(String tableName) {
        StringBuilder sb = new StringBuilder("SELECT column_name ")
            .append("FROM INFORMATION_SCHEMA.COLUMNS ")
            .append("WHERE table_schema = :TABLESCHEMA AND column_key = 'PRI' AND table_name = :TABLENAME");
        
        Query query = getSession().createSQLQuery(sb.toString());
        query.setParameter("TABLESCHEMA", getProperty(NAME));
        query.setParameter("TABLENAME", tableName);
        return (String) query.list().get(0);
    }

    public String getColumnType(String tableName, String columnName) {
        StringBuilder sb = new StringBuilder("SELECT column_type ")
            .append("FROM INFORMATION_SCHEMA.COLUMNS ")
            .append("WHERE table_schema = :TABLESCHEMA AND column_name = :COLUMNNAME AND table_name = :TABLENAME");
        
        Query query = getSession().createSQLQuery(sb.toString());
        query.setParameter("TABLESCHEMA", getProperty(NAME));
        query.setParameter("TABLENAME", tableName);
        query.setParameter("COLUMNNAME", columnName);
        return (String) query.list().get(0);
    }
}